package com.project.erp.action.contract.trade;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.Region;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.project.erp.model.contract.trade.TradeCusContract;

/**
 * 
 * COPYRIGHT (C) 2013 BY XXX公司

 *  功能模块名：合同-客户合同

 *  功能描述：导出excel工具类

 * @author LiJiaGuo    

 * @version 1.0  

 * @created 2014-1-14 下午9:30:09
 */
public class TradeCusContractExcel {
	protected final Logger logger = LoggerFactory.getLogger(getClass());
	private TradeCusContract tradeCusContract;
	private String commerinvoice;
	private String to;
	
	public TradeCusContractExcel(TradeCusContract tradeCusContract,String commerinvoice,String to){
		this.tradeCusContract = tradeCusContract;
		this.commerinvoice = commerinvoice;
		this.to = to;
	}
	
	/**
	 * 取得规则excel的输出流
	 * @return
	 */
	public ByteArrayInputStream getExcelOutputStream(){
		//创建工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();  
		//创建工作表
		HSSFSheet sheet = workbook.createSheet("sheet1");
		//设置列宽
		sheet.setColumnWidth(0, 4646);
		sheet.setColumnWidth(1, 5011);
		sheet.setColumnWidth(2, 2707);
		sheet.setColumnWidth(3, 2816);
		sheet.setColumnWidth(4, 4792);
		sheet.setColumnWidth(5, 2341);
		sheet.setColumnWidth(6, 3329);
		sheet.setColumnWidth(7, 3658);
		sheet.setColumnWidth(8, 4280);
		sheet.setColumnWidth(9, 4280);
		sheet.setColumnWidth(10, 7426);
		/**
		574+704=1278   21000*1278/574 = 46756
		sheet.setColumnWidth(1, 130); 46756*130/1278 = 4756
		sheet.setColumnWidth(1, 140); 46756*140/1278 = 5121
		sheet.setColumnWidth(2, 77); 46756*77/1278 = 2817
		sheet.setColumnWidth(3, 80); 46756*80/1278 = 2926
		sheet.setColumnWidth(4, 134); 46756*134/1278 = 4902
		sheet.setColumnWidth(5, 67); 46756*67/1278 = 2451
		sheet.setColumnWidth(6, 94); 46756*94/1278 = 3439
		sheet.setColumnWidth(7, 103); 46756*103/1278 = 3768
		sheet.setColumnWidth(8, 120); 46756*120/1278 = 4390
		sheet.setColumnWidth(9, 120); 46756*120/1278 = 4390
		sheet.setColumnWidth(10, 206);46756*206/1278 = 7536
		*/
		
		ByteArrayInputStream bais = null;
		try {
			//进出口公司
			HSSFRow inexpComNameRow = sheet.createRow(0);
			inexpComNameRow.setHeightInPoints((short)50);//设置行高
			HSSFCell inexpComNameCell = inexpComNameRow.createCell(0);
			HSSFCellStyle inexpComNameStyle = workbook.createCellStyle();
			HSSFFont inexpComNameFont = workbook.createFont();
			inexpComNameStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
			//inexpComNameFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体变粗
			inexpComNameFont.setFontHeightInPoints((short)40);//高度
			inexpComNameStyle.setFont(inexpComNameFont);
			inexpComNameCell.setCellStyle(inexpComNameStyle);
			inexpComNameCell.setCellValue(this.tradeCusContract.getInexpComName());//设置内容
			sheet.addMergedRegion(new Region(0, (short)0, 0, (short)11));//合并第一行的11个单元格
			
			//进出口地址
			HSSFRow inexpComAddressRow = sheet.createRow(1);
			inexpComAddressRow.setHeightInPoints((short)24);//设置行高
			HSSFCell inexpComAddressCell = inexpComAddressRow.createCell(0);
			HSSFCellStyle inexpComAddressStyle = workbook.createCellStyle();
			HSSFFont inexpComAddressFont = workbook.createFont();
			inexpComAddressStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
			inexpComAddressFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体变粗
			inexpComAddressFont.setFontHeightInPoints((short)15);//设置字体大小
			//inexpComAddressFont.setUnderline(HSSFFont.U_SINGLE);//设置下划线
			inexpComAddressStyle.setFont(inexpComAddressFont);
			inexpComAddressCell.setCellStyle(inexpComAddressStyle);
			inexpComAddressCell.setCellValue(this.tradeCusContract.getInexpComAddress());//设置内容
			sheet.addMergedRegion(new Region(1, (short)0, 1, (short)11));//合并第一行的11个单元格
			
			//己方公司地址
			HSSFRow companyAddressRow = sheet.createRow(2);
			companyAddressRow.setHeightInPoints((short)24);//设置行高
			HSSFCell companyAddressCell = companyAddressRow.createCell(0);
			HSSFCellStyle companyAddressStyle = workbook.createCellStyle();
			HSSFFont companyAddressFont = workbook.createFont();
			companyAddressStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
			companyAddressFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体变粗
			companyAddressFont.setFontHeightInPoints((short)15);//设置字体大小
			//companyAddressFont.setUnderline(HSSFFont.U_SINGLE);//设置下划线
			companyAddressStyle.setFont(companyAddressFont);
			companyAddressCell.setCellStyle(companyAddressStyle);
			companyAddressCell.setCellValue(this.tradeCusContract.getCompanyAddress());//设置内容
			sheet.addMergedRegion(new Region(2, (short)0, 2, (short)11));//合并第一行的11个单元格
			
			//Email,mobile,tel,fax
			HSSFRow emtfRow = sheet.createRow(3);
			emtfRow.setHeightInPoints((short)24);//设置行高
			HSSFCell emtfCell = emtfRow.createCell(0);
			HSSFCellStyle emtfStyle = workbook.createCellStyle();
			HSSFFont emtfFont = workbook.createFont();
			emtfStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
			emtfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体变粗
			//emtfFont.setUnderline(HSSFFont.U_SINGLE);//设置下划线
			emtfFont.setFontHeightInPoints((short)15);
			emtfStyle.setFont(emtfFont);
			emtfCell.setCellStyle(emtfStyle);
			emtfCell.setCellValue(this.tradeCusContract.getCompanyEmail()+"    "+this.tradeCusContract.getCompanyMob()+"    "+
					this.tradeCusContract.getCompanyTel()+"    "+this.tradeCusContract.getCompanyFax());//设置内容
			sheet.addMergedRegion(new Region(3, (short)0, 3, (short)11));//合并第一行的11个单元格
			
			//commerinvoice
			HSSFRow commerInvoiceRow = sheet.createRow(4);
			commerInvoiceRow.setHeightInPoints((short)70);//设置行高
			HSSFCell commerInvoiceCell = commerInvoiceRow.createCell(0);
			HSSFCellStyle commerInvoiceStyle = workbook.createCellStyle();
			HSSFFont commerInvoiceFont = workbook.createFont();
			commerInvoiceStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
			//commerInvoiceFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体变粗
			commerInvoiceFont.setUnderline(HSSFFont.U_SINGLE);//设置下划线
			commerInvoiceFont.setFontHeightInPoints((short)34);//高度
			commerInvoiceStyle.setFont(commerInvoiceFont);
			commerInvoiceCell.setCellStyle(commerInvoiceStyle);
			commerInvoiceCell.setCellValue(this.commerinvoice);//设置内容
			sheet.addMergedRegion(new Region(4, (short)0, 4, (short)11));//合并第一行的11个单元格
			
			//to
			HSSFRow cusRow = sheet.createRow(5);
			cusRow.setHeightInPoints((short)50);//设置行高
			HSSFCell toCell = cusRow.createCell(0);
			HSSFCellStyle toStyle = workbook.createCellStyle();
			HSSFFont toFont = workbook.createFont();
			toFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体变粗
			toFont.setFontHeightInPoints((short)17);
			toStyle.setFont(toFont);
			toStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			toCell.setCellStyle(toStyle);
			toCell.setCellValue(this.to);//设置内容

			//cusAddress
			//HSSFRow cusAddressRow = sheet.createRow(5);
			//cusAddressRow.setHeightInPoints((short)63);//设置行高
			HSSFCell cusAddressCell = cusRow.createCell(1);
			HSSFCellStyle cusAddressStyle = workbook.createCellStyle();
			HSSFFont cusAddressFont = workbook.createFont();
			cusAddressFont.setFontHeightInPoints((short)15);
			cusAddressFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体变粗
			cusAddressStyle.setFont(cusAddressFont);
			cusAddressStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			cusAddressStyle.setWrapText(true);//自动换行
			cusAddressCell.setCellStyle(cusAddressStyle);
			cusAddressCell.setCellValue(this.tradeCusContract.getCusAddress());//设置内容
			sheet.addMergedRegion(new Region(5, (short)1, 6, (short)4));//合并
			
			//cusTelFax
			/**
			HSSFRow cusTelFaxRow = sheet.createRow(6);
			cusTelFaxRow.setHeightInPoints((short)50);
			HSSFCell cusTelFaxCell = cusTelFaxRow.createCell(1);
			HSSFCellStyle cusTelFaxStyle = workbook.createCellStyle();
			HSSFFont cusTelFaxFont = workbook.createFont();
			cusTelFaxFont.setFontHeightInPoints((short)15);
			cusTelFaxFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体变粗
			cusTelFaxStyle.setFont(cusTelFaxFont);
			cusTelFaxStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			cusTelFaxStyle.setWrapText(true);//自动换行
			cusTelFaxCell.setCellStyle(cusTelFaxStyle);
			cusTelFaxCell.setCellValue(this.tradeCusContract.getCusTelFax());//设置内容
			sheet.addMergedRegion(new Region(6, (short)1, 6, (short)4));//合并
			*/
			//cusEmail
			HSSFRow cusEmailRow = sheet.createRow(7);
			cusEmailRow.setHeightInPoints((short)50);
			HSSFCell cusEmailCell = cusEmailRow.createCell(1);
			HSSFCellStyle cusEmailStyle = workbook.createCellStyle();
			HSSFFont cusEmailFont = workbook.createFont();
			cusEmailFont.setFontHeightInPoints((short)15);
			cusEmailFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体变粗
			cusEmailStyle.setFont(cusEmailFont);
			cusEmailStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			cusEmailStyle.setWrapText(true);//自动换行
			cusEmailCell.setCellStyle(cusEmailStyle);
			cusEmailCell.setCellValue(this.tradeCusContract.getCusTelFax()+"	"+this.tradeCusContract.getCusEmail());//设置内容
			
			sheet.addMergedRegion(new Region(7, (short)1, 7, (short)4));//合并
			
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
	        try {
	            workbook.write(baos);
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
	        byte[] ba = baos.toByteArray();
	        bais = new ByteArrayInputStream(ba);
		} catch (Exception e) {
			logger.error("ExcelUtils getExcelBook Error:"+e.toString());
		}
        
        return bais; 
	}
}
